package com.zg.autoform.util;

import cn.hutool.core.util.StrUtil;
import com.zg.autoform.enm.ColumnSizeEnum;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author Zxb
 */
@Slf4j
@Configuration
public class TableUtil {

    @Value("${dds.default.jdbc-url}")
    private String JDBC_URL;

    @Value("${dds.default.username}")
    private String USERNAME;

    @Value("${dds.default.password}")
    private String PASSWORD;

    /**
     * 建表
     * 参数：tableName 表名
     */
    public int createTable(String tableName) {
        if (StrUtil.isBlank(tableName)) {
            return -1;
        }
        tableName = tableName.toUpperCase();
        Connection conn = null;
        Statement stmt = null;
        try {
            // 注册驱动程序
            Class.forName("oracle.jdbc.driver.OracleDriver");

            // connection
            try {
                conn = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
            } catch (SQLException e) {
                return -3;
            }

            // 是否存在表
            DatabaseMetaData metaData = conn.getMetaData();
            String[] types = {"TABLE"};
            ResultSet tables = metaData.getTables(null, null, tableName.toString(), types);
            if (tables.next()) {
                return -2;
            }

            stmt = conn.createStatement();

            String sql = "CREATE TABLE " + tableName.toString() +
                    "(ID NUMBER(20) NOT NULL, " +
                    " CREATE_BY NVARCHAR2(50), " +
                    " UPDATE_BY NVARCHAR2(50), " +
                    " VER NUMBER(11), " +
                    " IS_DEL NUMBER(4), " +
                    " SEARCH_STR NVARCHAR2(255), " +
                    " FLOW_INST_CODE NVARCHAR2(255), " +
                    " CREATE_TIME DATE, " +
                    " UPDATE_TIME DATE, " +
                    " PRIMARY KEY(ID))";
            log.info(new Date() + " 建表语句：\n" + sql);
            // execute
            stmt.executeUpdate(sql);
        } catch (SQLException | ClassNotFoundException e) {
            log.error(" *** 建表错误：" + tableName + "\n  time : " + new Date() + e.toString());
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 1;
    }

    /**
     * 查询所有字段
     * 参数：tableName 表名
     */
    public List<String> selectColumn(String tableName) {
        if (StrUtil.isBlank(tableName)) {
            return null;
        }
        tableName = tableName.toUpperCase();

        List<String> columns = new ArrayList<>();
        Connection conn = null;
        Statement stmt = null;
        try {
            // 注册驱动程序
            Class.forName("oracle.jdbc.driver.OracleDriver");

            // connection
            try {
                conn = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
            } catch (SQLException e) {
                return null;
            }

            // 是否存在表
            DatabaseMetaData metaData = conn.getMetaData();
            String[] types = {"TABLE"};
            ResultSet tables = metaData.getTables(null, null, tableName.toString(), types);
            if (!tables.next()) {
                return null;
            }

            stmt = conn.createStatement();
            // execute
            ResultSet resultSet = stmt.executeQuery("SELECT column_name FROM all_tab_columns WHERE table_name = '" + tableName + "'");
            // 遍历结果集并输出字段名
            while (resultSet.next()) {
                columns.add(resultSet.getString("column_name"));
                System.out.println("Column Name: " + resultSet.getString("column_name"));
            }
            log.info(new Date() + " 查询字段语句：\n" + "SELECT column_name FROM all_tab_columns WHERE table_name =" + tableName);
        } catch (SQLException | ClassNotFoundException e) {
            log.error(" *** 查询字段错误：" + tableName + "," + "\n  time : " + new Date() + e.toString());
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return columns;
    }

    /**
     * 插入字段
     * 参数：tableName 表名 , columnName 字段 , type 字段类型（详见字典表 ColumnTypeEnum）
     * return -1 判空， -2 表不存在 ， -3 连接失败 ，-4 字段类型不存在，-5 字段已存在
     */
    public int insertColumn(String tableName, String columnName, String type, String size) {
        if (StrUtil.isBlank(tableName) || StrUtil.isBlank(columnName) || StrUtil.isBlank(type)) {
            return -1;
        }
        if (ColumnSizeEnum.isExists(type)) {
            return -4;
        }
        tableName = tableName.toUpperCase();
        columnName = columnName.toUpperCase();
        Connection conn = null;
        Statement stmt = null;
        String fieldType = null;
        String alterTableSql = null;
        try {
            // 注册驱动程序
            Class.forName("oracle.jdbc.driver.OracleDriver");

            // connection
            try {
                conn = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
            } catch (SQLException e) {
                return -3;
            }

            // 是否存在表
            DatabaseMetaData metaData = conn.getMetaData();
            String[] types = {"TABLE"};
            ResultSet tables = metaData.getTables(null, null, tableName.toString(), types);
            if (!tables.next()) {
                // 先建表
                try {
                    createTable(tableName);
                } catch (Exception e) {
                    return -2;
                }
            }

            stmt = conn.createStatement();
            // 是否存在字段
            if (isExistsOfField(stmt, tableName, columnName)) {
                return 1;
            }
            ColumnSizeEnum columnSizeEnum = ColumnSizeEnum.getSizeByName(type);
            fieldType = columnSizeEnum.getType();
            if (null != columnSizeEnum.getSize()) {
                if (StrUtil.isNotBlank(size) && Double.valueOf(size) > 0 && Double.valueOf(size) < Double.valueOf(columnSizeEnum.getSize())) {

                } else {
                    size = columnSizeEnum.getSize();
                }
                alterTableSql = "ALTER TABLE " + tableName.toString() +
                        " ADD ( " + columnName +
                        " " + fieldType + "(" + size + "))";
            } else {
                alterTableSql = "ALTER TABLE " + tableName.toString() +
                        " ADD ( " + columnName +
                        " " + fieldType + ")";
            }
            log.info(new Date() + " 插入字段语句：\n" + alterTableSql);
            // execute
            stmt.execute(alterTableSql);
        } catch (SQLException | ClassNotFoundException e) {
            log.error(" *** 插入字段错误：" + tableName + "," + columnName + "(" + fieldType + ")" + "\n  time : " + new Date() + e.toString());
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 1;
    }

    /**
     * 删除字段
     * 参数：tableName 表名 , columnName 字段
     * return -1 判空， -2 表不存在 ， -3 连接失败 ，-4 字段已存在
     */
    public int deleteColumn(String tableName, String columnName) {
        if (StrUtil.isBlank(tableName) || StrUtil.isBlank(columnName)) {
            return -1;
        }
        tableName = tableName.toUpperCase();
        columnName = columnName.toUpperCase();
        Connection conn = null;
        Statement stmt = null;
        try {
            // 注册驱动程序
            Class.forName("oracle.jdbc.driver.OracleDriver");

            // connection
            try {
                conn = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
                conn.getNetworkTimeout();
            } catch (SQLException e) {
                return -3;
            }

            // 是否存在表
            DatabaseMetaData metaData = conn.getMetaData();
            String[] types = {"TABLE"};
            ResultSet tables = metaData.getTables(null, null, tableName.toString(), types);
            if (!tables.next()) {
                return -2;
            }

            stmt = conn.createStatement();

            String alterTableSql = "ALTER TABLE " + tableName.toString() + " DROP COLUMN " + columnName;
            log.info(new Date() + " 删除字段语句：\n" + alterTableSql);
            // 是否存在字段
            if (!isExistsOfField(stmt, tableName, columnName)) {
                return -4;
            }
            // execute
            stmt.execute(alterTableSql);
        } catch (SQLException | ClassNotFoundException e) {
            log.error(" *** 删除字段错误：" + tableName + "," + columnName + "\n  time : " + new Date() + e.toString());
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 1;
    }

    private boolean isExistsOfField(Statement stmt, String tableName, String columnName) throws SQLException {
        ResultSet rs = stmt.executeQuery("SELECT column_name FROM all_tab_columns WHERE table_name = '" + tableName + "' AND column_name = '" + columnName + "'");
        if (rs.next()) {
            return true;
        }
        return false;
    }

}
